# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################


import time
import json
import utils.pgsql.base.pgconn as pgconn

"""
channel code table  渠道表
add 
delete
modify
...
"""
_tblName = "tbl_channel"

_DB_SCRIPT = """
CREATE TABLE IF NOT EXISTS {} (
                "channelId"  SERIAL PRIMARY KEY ,    
                "caseId"  INTEGER NOT NULL default 0 ,    
                "operatorId"  INTEGER NOT NULL default 0 ,    
                "channelName"  VARCHAR(260) NOT NULL default '',    
                "pagePath"  VARCHAR(260) NOT NULL default '',    
                "description" VARCHAR(260) NOT NULL default '',    
                "sellerId"  INTEGER NOT NULL default 0 ,    
                "marketingShare" VARCHAR(260) NOT NULL default '',   
                "discount" VARCHAR(260) NOT NULL default '',    
                "expiryDate" json,  
                "pointId" VARCHAR(260) NOT NULL default '',    
                "orCode" VARCHAR(260) NOT NULL default '',  
                "createTime" VARCHAR(32) NOT NULL default '',
                "updateTime" VARCHAR(32) NOT NULL default ''
                );

""".format(
    _tblName
)


class PgChannel:
    def __init__(self):
        self._pgConn = pgconn.pgMainConn

    def __del__(self):
        pass

    def createTbl(self):
        """
        create
        :return:
        """
        return self._pgConn.exec(_DB_SCRIPT)

    
    def addChannel(self, dic) -> str:
        
        dic["createTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        if "expiryDate" in dic:
            if dic["expiryDate"]!=None:
                dic["expiryDate"] = json.dumps(dic["expiryDate"])
            else:
                del dic["expiryDate"]

        sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'
       
        sql = f"""insert into {_tblName} ({sql_key}) values ({sql_val[:-1]}) RETURNING "{"channelId"}" """
        return pgconn.pgMainConn.exec_fetchone(sql)

    def modify(self, ids, dic) -> bool:
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

        if "expiryDate" in dic:
            dic["expiryDate"] = json.dumps(dic["expiryDate"])

        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
            else:
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'

        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1]) + " \"channelId\" = '{}' ".format(
            ids
        )
        return self._pgConn.exec(sql)

    
    def delete(self, ids) -> bool:
        sql = "delete from {} where \"channelId\" = '{}' ".format(_tblName, ids)
        return self._pgConn.exec(sql)

    
    def sellerId(self, sellerId) -> list:
        sql = "select * from {} where \"sellerId\" = '{}' ".format(_tblName, sellerId)
        return self._pgConn.exec_fetchall(sql)

    
    def caseorcode(self, channelName, caseId) -> list:
        sql = (
            "select * from {} where \"pagePath\" = '{}' and \"caseId\" = '{}' ".format(
                _tblName, channelName, caseId
            )
        )
        return self._pgConn.exec_fetchall(sql)

    
    def channelname(self, pagePath, caseId) -> list:
        sql = "select * from {} where \"channelName\" = '{}' and \"caseId\" = '{}' ".format(
            _tblName, pagePath, caseId
        )
        return self._pgConn.exec_fetchall(sql)

    
    def channeleditname(self, pagePath, caseId, channelId) -> list:
        sql = "select * from {} where \"channelName\" = '{}' and \"caseId\" = '{}' and \"channelId\" != '{}' ".format(
            _tblName, pagePath, caseId, channelId
        )
        return self._pgConn.exec_fetchall(sql)

    
    def all(self) -> list:
        sql = 'select a.*,b."firmName",n."operatorName",c."caseName",p."name" from {} a left join tbl_operator n on a."operatorId" = n."operatorId" left join tbl_seller b on a."sellerId" = b."sellerId" left join tbl_case c  on a."caseId" = c."caseId" left join playground p on a."caseId" = p."case_id"'.format(
            _tblName
        )
        return self._pgConn.exec_fetchall(sql)

    def all_cases(self) -> list:
        sql = 'select a.*,b."firmName",n."operatorName",c."caseName" from {} a left join tbl_operator n on a."operatorId" = n."operatorId" left join tbl_seller b on a."sellerId" = b."sellerId" left join tbl_case c  on a."caseId" = c."caseId" '.format(
            _tblName
        )
        return self._pgConn.exec_fetchall(sql)

    def all_ByCaseIds(self, names) -> list:
        sql = 'select a.*,b."firmName",n."operatorName",c."caseName" from {} a left join tbl_operator n on a."operatorId" = n."operatorId" left join tbl_seller b on a."sellerId" = b."sellerId" left join tbl_case c  on a."caseId" = c."caseId" where c."caseName" in {} '.format(
            _tblName, tuple(names)
        )
        return self._pgConn.exec_fetchall(sql)

    def allByoperator(self, operatorId) -> list:
        sql = 'select a.*,n."operatorName",b."firmName",c."caseName" from {} a left join tbl_operator n on a."operatorId" = n."operatorId"  left join tbl_seller b on a."sellerId" = b."sellerId" left join tbl_case c on a."caseId" = c."caseId" where a."operatorId" = \'{}\' '.format(
            _tblName, operatorId
        )
        return self._pgConn.exec_fetchall(sql)

    def allBycaseId(self, caseId) -> list:
        sql = 'select a.*,n."operatorName",b."firmName",c."caseName" from {} a left join tbl_operator n on a."operatorId" = n."operatorId" left join tbl_seller b on a."sellerId" = b."sellerId" left join tbl_case c on a."caseId" = c."caseId" where  a."caseId" = \'{}\''.format(
            _tblName, caseId
        )
        return self._pgConn.exec_fetchall(sql)

    def allBycaseIdchannel(self, caseId) -> list:
        sql = 'select a.*,n."operatorName",b."firmName",c."caseName" from {} a left join tbl_operator n on a."operatorId" = n."operatorId" left join tbl_seller b on a."sellerId" = b."sellerId" left join tbl_case c on a."caseId" = c."caseId" where  a."caseId" = \'{}\''.format(
            _tblName, caseId
        )
        return self._pgConn.exec_fetchall(sql)

    def allunchannelcase(self, operatorId, caseId) -> list:
        sql = 'select a.*  from tbl_seller a left join {}  b on a."sellerId"=b."sellerId" where b."sellerId" is null and a."firmAccount" = \'{}\' and a."caseId" = \'{}\' '.format(
            _tblName, operatorId, caseId
        )
        return self._pgConn.exec_fetchall(sql)

    def allunchanneloperator(self, operatorId) -> list:
        sql = 'select a.* from tbl_seller a left join {}  b on a."sellerId"=b."sellerId" where b."sellerId" is null and a."firmAccount" = \'{}\' and  a."caseId" != 0 '.format(
            _tblName, operatorId
        )
        return self._pgConn.exec_fetchall(sql)

    def channelInfo(self, channelId) -> list:
        sql = f""" select a."channelId",a."caseId",a."operatorId",b."sellerId",b."firmName",b."firmAccount",b."proportion"
                  from tbl_channel as a , tbl_seller as b
                  where
                  cast(a."operatorId" as VARCHAR) = b."firmAccount"
                  and
                  a."channelId" = {channelId}     
                  and
                  a."sellerId" = b."sellerId"
                  """
        return self._pgConn.exec_fetchall(sql)

    def caseInfo(self, caseId) -> list:
        sql = f""" select tbl_case."caseName",tbl_case."operatorId",tbl_seller."firmName",tbl_seller."proportion"
                   from tbl_case,tbl_seller
                   where 
                   tbl_case."caseId"={caseId} 
                   and 
                   cast(tbl_case."operatorId" as VARCHAR) = tbl_seller."firmAccount"
                   and
                   tbl_seller."accounttype" = '企业账号'                   
                   """
        return self._pgConn.exec_fetchall(sql)

    # 
    

    # def update_adminId(self, channelId, adminId) -> list:
    #     sql = "update {} set \"adminId\" = \'{}\' where \"channelId\" = \'{}\'".format(
    #         _tblName, adminId, channelId)
    #     return pgconn.pgMainConn.exec(sql)

    # 
    
    # def all(self) -> list:
    #     sql = "select * from {}".format(_tblName)
    #     return self._pgConn.exec_fetchall(sql)

    # 
    
    # def get(self, ids) -> dict:
    #     sql = "select * from {} where \"channelId\" = \'{}\'".format(
    #         _tblName, ids)
    #     return self._pgConn.exec_fetchone(sql)

    #
    
    # def getUnusedChannel(self) -> list:
    #     sql = "select \"channelId\",\"channelName\" from {} where \"adminId\" = \'0\'".format(
    #         _tblName)
    #     return self._pgConn.exec_fetchall(sql)

    # 
    
    # def modify(self, dic, keys, ids) -> bool:
    #     dic['updateTime'] = time.strftime(
    #         "%Y-%m-%d %H:%M:%S", time.localtime())

    #     sql_key = ''
    #     for key in dic:
    #         if type(dic[key]) is str:
    #             sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
    #         else:
    #             sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'


    #     sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1])  + " \"{}\" = \'{}\' ".format(keys, ids)
    #     return self._pgConn.exec(sql)

    # 
    
    # def delete(self, ids) -> bool:
    #     sql = "delete from {} where \"channelId\" = \'{}\' ".format(
    #         _tblName, ids)
    #     return self._pgConn.exec(sql)
